<?php
/**
 * Display the split of offers by status and compare to last year and same 
 * month information
 * 
 */
use Fox\Html\DropList\Radio;
use Fox\SalesDistribution\Sales\Partner\Role;
use Fox\Grid\FieldCat\Field;
use Fox\Html\DropList;
use Fox\Grid\FieldCat;
use Fox\SalesDistribution\Sales\Inquiry\Status;
use Fox\SalesDistribution\Sales\Inquiry;
use Doctrine\DBAL\Types\Type;
$xview = Xview::instance();

$screen = new Fox\Html\Screen("eSelect","execute");
$block = new Fox\Html\Block("gCriteria",Msg(Fox::MSGCL,29));
$block->add(new Fox\Html\Input("date",false,time()));
$block->add(new DropList("last_date_comparison",false));
$block->add(new Radio("output_type",true,true,1))->addAllOption();
$screen->add($block);
if($screen->submitted())
{
	$inputs = $screen->get();
	if(!$screen->errors())
	{
		// Init Grid
		$grid = new Fox\Grid("gReport");
		$grid->fieldcat(array(array("status","label"),
								
								array("amount","amount_n"),
								array("rate","amount_rate_n"),
								array("number","number_n"),
								array("rate","rate_n"),
								
								array("amount","amount_nm1"),
								array("rate","amount_rate_nm1"),
								array("number","number_nm1"),
								array("rate","rate_nm1")));
		$grid->setXlsDownload();
		$grid->setTotal();
		$grid->fieldcat()->field("label")->SetClass("separator-right")
										->width(4);
		$grid->fieldcat()->field("amount_rate_n")
							->SetClass("separator-right")
							->formater()->displayZeroValue(false);
		$grid->fieldcat()->field("rate_n")->SetClass("separator-right")
							->formater()->displayZeroValue(false);
		$grid->fieldcat()->field("amount_rate_nm1")->SetClass("separator-right")
							->formater()->displayZeroValue(false);
		
		$grid->fieldcat()->setFieldAttr(array("rate_n","rate_nm1","amount_rate_nm1","amount_rate_n"),"dtype","rate");
		
		$total_amount_n = $total_amount_nm1 = $total_nm1 = $total_n = 0;
		$finder = new Status();
		$status = $finder->find(new Fox\Dao\SqlFragment("statusid != ?",array(1)));
		foreach($status as $item)
		{
			$data[$item->getId()] = array("status" => $item->getId(),
										  "label" => $item->label(),
										  "amount_n" => 0,
			 							  "amount_nm1" => 0,
										  "amount_rate_n" => 0,
										  "amount_rate_nm1" => 0,
										  "number_n" => 0,
										  "number_nm1" => 0,
										  "rate_n" => 0,
										  "rate_nm1" => 0,
										  "nb_n" => 0,
										  "nb_nm1" => 0);
		}
		
		$selectDate = new DateTime($inputs['date']);
		
		$query = \Fox\Dao::infos(Inquiry::className())->handle()->createQueryBuilder();
		
		$query->select('status','total1','total2')
				->from('inquiries','t')
				->innerJoin('t', 'inquiry_partners', 'pa','t.inquiryid = pa.inquiryid')
				;
				
		// Build the interval for month n year n
		$firstDay = new DateTime($selectDate->format("Y")."-01-01");
		
		$lastDay = new DateTime($selectDate->format("Y-m")."-01");
		$lastDay->add(new DateInterval("P1M"));
		
		// Label Grouping
		$fieldGroup1 = new Field("label1");
		$label_n = Fox\Ddic\DataElement\Text::label("month",$selectDate->format("m"))." - ".$selectDate->format("Y");
		$fieldGroup1->setClass("gd-col-group separator-right separator-left")->label($label_n);
		$grid->fieldcat()->buildSubTitleByGrouping(array("amount_n","amount_rate_n"),$fieldGroup1);
		$fieldGroup2 = new Field("label2");
		$fieldGroup2->setClass("gd-col-group")->label($label_n);
		$grid->fieldcat()->buildSubTitleByGrouping(array("number_n","rate_n"),$fieldGroup2);
		
		$fragments[] = new Fox\Dao\SqlFragment("start_date >= ? AND start_date < ? ",array($firstDay,$lastDay),array(Type::DATE,Type::DATE));
		$fragments[] = new Fox\Dao\SqlFragment("t.loekz = ? AND pa.role = ?",array(0,Role::RESPONSIBLE_AGENCY));
		if(!Fox\Authorization\Manager::check(User::AUTH_VIEW_ALL))
			$fragments[] = User::current()->inquiryFragments();
		$fragment = Fox\Dao\SqlFragment::combine($fragments);
		$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);

		foreach($result as $item)
		{
			$status = $item['status'];
			$amount = $item['total1'] ;
			$data[$status]['amount_n'] += $amount;
			$data[$status]['number_n'] += 1;
			$total_n += 1;
			$total_amount_n += $amount;
		}
		
		// Build the interval for month n-1 year n-1
		$query = \Fox\Dao::infos(Inquiry::className())->handle()->createQueryBuilder();
		$query->select('status','total1')
				->from('inquiries','t')
				->innerJoin('t', 'inquiry_partners', 'pa','t.inquiryid = pa.inquiryid');
		if($inputs['last_date_comparison'] == "P1Y")
		{
			$firstDayLastYear = new DateTime($firstDay->format("Y-m-d"));
			
			$firstDayLastYear->sub(new DateInterval($inputs['last_date_comparison']));
			$lastDayLastYear = new DateTime($lastDay->format("Y-m-d"));
			$lastDayLastYear->sub(new DateInterval($inputs['last_date_comparison']));
			$mtnNum = $selectDate->format("m");
			$selectDate->sub(new DateInterval($inputs['last_date_comparison']));
			
			$label_nm1 = Fox\Ddic\DataElement\Text::label("month",$mtnNum)." - ".$selectDate->format("Y");
		}
		elseif($inputs['last_date_comparison'] == "P1M")
		{
			$firstDayLastYear = $firstDay;
			$lastDayLastYear = new DateTime($lastDay->format("Y-m-d"));
			$mtnNum = $selectDate->format("m") - 1;
			if($mtnNum == 0)
				$mtnNum = 12;
			$lastDayLastYear->sub(new DateInterval($inputs['last_date_comparison']));
			$selectDate->sub(new DateInterval($inputs['last_date_comparison']));
			
			$label_nm1 = Fox\Ddic\DataElement\Text::label("month",$mtnNum)." - ".$selectDate->format("Y");
		}
		$fieldGroup3 = new Field("label3");
		$fieldGroup3->setClass("gd-col-group separator-right separator-left")
					->label($label_nm1);
		
		$grid->fieldcat()->buildSubTitleByGrouping(array("amount_nm1","amount_rate_nm1"),$fieldGroup3);
		$fieldGroup4 = new Field("label4");
		$fieldGroup4->setClass("gd-col-group ")->label($label_nm1);
		$grid->fieldcat()->buildSubTitleByGrouping(array("number_nm1","rate_nm1"),$fieldGroup4);
		$fragments = null;
		$fragments[] = new Fox\Dao\SqlFragment("start_date >= ? AND start_date < ? ",array($firstDayLastYear,$lastDayLastYear),array(Type::DATE,Type::DATE));
		$fragments[] = new Fox\Dao\SqlFragment("t.loekz = ? AND pa.role = ?",array(0,Role::RESPONSIBLE_AGENCY));
		if(!Fox\Authorization\Manager::check(User::AUTH_VIEW_ALL))
			$fragments[] = User::current()->inquiryFragments();
		$fragment = Fox\Dao\SqlFragment::combine($fragments);
		$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);

		foreach($result as $item)
		{
			$status = $item['status'];
			$amount = $item['total1'];
			$data[$status]['amount_nm1'] += $amount;
			$data[$status]['number_nm1'] += 1;
			$total_amount_nm1 += $amount;
			$total_nm1 += 1;
		}

		foreach($data as $key => $item)
		{
			if($total_n > 0)
				$data[$key]['rate_n'] = $item['number_n'] / $total_n *100;
			if($total_nm1 > 0)
				$data[$key]['rate_nm1'] = $item['number_nm1'] / $total_nm1 *100;
			if($total_amount_nm1 > 0)
				$data[$key]['amount_rate_nm1'] = $item['amount_nm1'] / $total_amount_nm1 *100;
			if($total_amount_n > 0)
				$data[$key]['amount_rate_n'] = $item['amount_n'] / $total_amount_n *100;
		}
		
			
		$grid->setDatas($data);
		$output = null;
		if($inputs['output_type'] == 1 || $inputs['output_type'] == DropList::ALL)
		{
			$output .= $grid->Display();
		}
		if($inputs['output_type'] == 0 || $inputs['output_type'] == DropList::ALL)
		{
			// Colors
			$colors  = "&colors[]=".urlencode("#3366cc");
			$colors .= "&colors[]=".urlencode("#109618");
			$colors .= "&colors[]=".urlencode("#dc3912");
			$colors .= "&colors[]=".urlencode("#fd9800");
			// Legends
			$legends = null;
			foreach($data as $line)
			{
				$legends .= "&legends[]=".urlencode($line['label']);
			}
			$static = "?mod=charts&act=pie&xvtrs=none&colors=$colors&legends=$legends";
			
			// Build Graphs 1 (Dispatch By Amount Year n
			foreach($data as $line)
			{
				$graphData1[] = $line['amount_rate_n'];
				$graphData2[] = $line['amount_rate_nm1'];
				$graphData3[] = $line['rate_n'];
				$graphData4[] = $line['rate_nm1'];
			}
			$graphData1 = json_encode($graphData1);
			$graphData2 = json_encode($graphData2);
			$graphData3 = json_encode($graphData3);
			$graphData4 = json_encode($graphData4);
			$title = "R&eacute;partion des montants - ".$label_n;
			$output .= "<div>";
			$output .= "<img style=\"padding:5px 5px\" src=\"$static&data=$graphData1&title=$title\" >";
			$title = "R&eacute;partion des montants - ".$label_nm1;
			$output .= "<img style=\"padding:5px 5px\" src=\"$static&data=$graphData2&title=$title\" >";
			$title = "R&eacute;partion des nombres - ".$label_n;
			$output .= "<br><img style=\"padding:5px 5px\" src=\"$static&data=$graphData3&title=$title\" >";
			$title = "R&eacute;partion des nombres - ".$label_nm1;
			$output .= "<img style=\"padding:5px 5px\" src=\"$static&data=$graphData4&title=$title\" >";
			$output .= "</div>";
		
			
		}
	}
}
echo '<h1>'.$xview->pageTitle().'</h1>';
echo $screen->Display();
if(isset($output))
	echo $output;
